package com.my.system.dao.product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.my.system.dao.DataBaseConnection;
import com.my.system.object.product.Product;

public class ProductDao {
	private static Connection con = null;
	private static PreparedStatement psta = null;
	private static ResultSet rs = null;
	static {
		con = DataBaseConnection.getConnection();
	}

	public static Product searchProductByCode(String code) {
		String sql = "select * from product where code=?";
		Product product = null;
		try {
			psta = con.prepareStatement(sql);
			psta.setString(1, code);
			rs = psta.executeQuery();
			if (rs.next()) {
				int id = rs.getInt(1);
				String code1 = rs.getString(2);
				String name = rs.getString(3);
				double price = rs.getDouble(4);
				int product_unit_id = rs.getInt(5);
				int catalog_id = rs.getInt(6);
				product = new Product(id, code1, name, price, product_unit_id,
						catalog_id);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (psta != null) {
					psta.close();
				}
				if (rs != null) {
					psta.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return product;
	}

	public static Product searchProductByID(int id2) {
		String sql = "select * from product where id=?";
		Product product = null;
		try {
			psta = con.prepareStatement(sql);
			psta.setInt(1, id2);
			rs = psta.executeQuery();
			if (rs.next()) {
				int id = rs.getInt(1);
				String code1 = rs.getString(2);
				String name = rs.getString(3);
				double price = rs.getDouble(4);
				int product_unit_id = rs.getInt(5);
				int catalog_id = rs.getInt(6);
				product = new Product(id, code1, name, price, product_unit_id,
						catalog_id);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (psta != null) {
					psta.close();
				}
				if (rs != null) {
					psta.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return product;
	}

	public static boolean insertProduct(Product product) {
		String sql = "insert into product(" + "code," + "name," + "price,"
				+ "product_unit_id," + "catalog_id)" + "values(?,?,?,?,?)";
		try {
			psta = con.prepareStatement(sql);
			psta.setString(1, product.getCode());
			psta.setString(2, product.getName());
			psta.setDouble(3, product.getPrice());
			psta.setInt(4, product.getProduct_unit_id());
			psta.setInt(5, product.getCatalog_id());
			con.setAutoCommit(false);
			int i = psta.executeUpdate();
			con.commit();
			con.setAutoCommit(true);
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (psta != null) {
					psta.close();
				}
				if (rs != null) {
					psta.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}

	public static List<Product> searchAllProduct() {
		List<Product> list = new ArrayList<Product>();
		String sql = "select * from product ";
		try {
			psta = con.prepareStatement(sql);
			rs = psta.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String code1 = rs.getString(2);
				String name = rs.getString(3);
				double price = rs.getDouble(4);
				int product_unit_id = rs.getInt(5);
				int catalog_id = rs.getInt(6);
				Product product = new Product(id, code1, name, price,
						product_unit_id, catalog_id);
				list.add(product);

			}
			if (list.size() > 0) {
				return list;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (psta != null) {
					psta.close();
				}
				if (rs != null) {
					psta.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return null;
	}
}
